SQL是一项重要的技能，可能是普通程序员在工作中最常用的。面试的时候造飞机大炮，最后可能还是得实实在在得写SQL。

Structured Query Language

背景

1970 年 IBM 的 E.F. Codd 博士发表了论文《A Relational Model of Data for Large Shared Data Banks》并创建了关系模型，通过一个简单的数据结构（关系，也就是二维表）来实现数据的存储。

1979 年 Relational Software, Inc.（后来改名为 Oracle）发布了第一个商用的关系数据库产品。随后出现了大量的关系数据库管理系统，包括 MySQL、SQL Server、PostgreSQL 以及大数据分析平台 Apache Hive、Spark SQL、Presto 等。至今，关系数据库仍然是数据库领域的主流。

以下是著名的数据库系统排名网站 DB-Engines 上各种数据库的排名情况，关系数据库占据了绝对的优势。

SQL（Structured Query Language，结构化查询语言）是访问和操作关系数据库的标准语言。只要是关系数据库，都可以使用 SQL 进行访问和控制。SQL 同样由 IBM 在 1970 年代开发，1986 年成为 ANSI 标准，并且在 1987 年成为 ISO 标准。SQL 标准随后经历了多次修订，最新的版本为 SQL:2019，增加了多维数组（MDA）的支持。下图是 SQL 标准的发展历程和主要的新增功能。

对于 SQL 标准，人们最熟悉的就是 SQL92 或者 SQL99。但实际上经过多次修改，SQL 早已不是 40 年前的 SQL；如今它已经相当完备，功能强大，并且能够同时支持关系模型和非关系（XML、JSON）模型。具体来说，最新的 SQL 标准包含 10 个部分：

ISO/IEC 9075-1 信息技术 – 数据库语言 – SQL – 第1部分：框架（SQL/框架）
ISO/IEC 9075-2 信息技术 – 数据库语言 – SQL – 第2部分：基本原则（SQL/基本原则）
ISO/IEC 9075-3 信息技术 – 数据库语言 – SQL – 第3部分：调用级接口（SQL/CLI）
ISO/IEC 9075-4 信息技术 – 数据库语言 – SQL – 第4部分：持久存储模块（SQL/PSM）
ISO/IEC 9075-9 信息技术 – 数据库语言 – SQL – 第9部分：外部数据管理（SQL/MED）
ISO/IEC 9075-10 信息技术 – 数据库语言 – SQL – 第10部分：对象语言绑定（SQL/OLB）
ISO/IEC 9075-11 信息技术 – 数据库语言 – SQL – 第11部分：信息与定义概要（SQL/Schemata）
ISO/IEC 9075-13 信息技术 – 数据库语言 – SQL – 第13部分：使用 Java 编程语言的 SQL 程序与类型（SQL/JRT）
ISO/IEC 9075-14 信息技术 – 数据库语言 – SQL – 第14部分：XML 相关规范（SQL/XML）
ISO/IEC 9075-15 信息技术 – 数据库语言 – SQL – 第15部分：多维数组（SQL/MDA）

为了便于学习，通常将主要的 SQL 语句分为以下几个类别：

DQL（data query language），数据查询语言；也就是 SELECT 语句，用于查询数据库中的数据和信息。
DML（data manipulation language），数据操作语言；用于对表中的数据进行增加（INSERT）、修改（UPDATE）、删除（DELETE）以及合并（MERGE）操作。
DDL（data definition language），数据定义语言；主要用于定义数据库中的对象（例如表或索引），包括创建对象（CREATE）、修改对象（ALTER）和删除对象（DROP）等。
TCL（transaction control language），事务控制语言；用于管理数据库的事务，主要包括启动一个事务（BEGIN TRANSACTION）、提交事务（COMMIT）、回退事务（ROLLBACK）和事务保存点（SAVEPOINT）。
DCL（data control language），数据控制语言；用于控制数据的访问权限，主要有授权（GRANT）和撤销（REVOKE）。

SQL 是一种标准，不同厂商基于 SQL 标准实现了自己的数据库产品，例如 Oracle、MySQL 等。这些数据库都在一定程度上兼容 SQL 标准，具有一定的可移植性。但另一方面，它们都存在许多专有的扩展，没有任何一种产品完全遵循标准。

NoSQL

随着互联网的发展和大数据的兴起，出现了各种各样的非关系（NoSQL）数据库。NoSQL 代表 Not only SQL，表明它是针对传统关系数据库的补充和升级，而不是为了替代关系数据库。

NoSQL 数据库主要用于解决关系数据库在某些特定场景下的局限性，比如海量存储和水平扩展；但同时也会为此牺牲某些关系数据库的特性，例如对事务强一致性的支持和标准 SQL 接口。因此，这类数据库主要用于对一致性要求不是非常严格的互联网业务。常见的 NoSQL 数据库可以分为以下几类：

文档数据库，例如 MongoDB（MongoDB 4.0 增加了多文档事务的特性）；
键值存储，例如 Redis；
全文搜索引擎，例如 Elasticsearch；
宽列存储数据库，例如 Cassandra；
图形数据库，例如 Neo4J。

另一方面，关系数据库也在积极拥抱变化，添加了许多非关系模型（XML 和 JSON）支持。以最流行的开源关系数据库 MySQL 为例，最新的 MySQL 8.0 版本增加了 JSON 文档存储的支持，并且推出了一个新的概念：NoSQL + SQL = MySQL。以下是 MySQL 官方的宣传图。

Oracle、SQL Server 以及 PostgreSQL 同样也进行了类似的扩展，可以支持原生的 XML 和 JSON 数据，并且提供了许多标准的 SQL 接口。

NewSQL

中国有句古话：天下大势，合久必分，分久必合。数据库领域的发展也印证了这一规律，为了同时获得关系数据库对于事务的支持和标准的 SQL 接口，以及非关系数据库的高度扩展性和高性能。如今市场上已经出现了一类新型关系型数据库系统：NewSQL 数据库。

比较有代表性的 NewSQL 数据库包括 Google Spanner、VoltDB、PostgreSQL-XL 以及国产的 TiDB。这类新型数据库是数据库领域最新的发展方向，有志于在数据库行业发展的同学可以加以关注。

为什么要学习 SQL？

让我们回到专栏的主题，为什么要学习 SQL 呢？简单来说，因为有用。下图是 Stack Overflow 在 2019 年关于最流行编程技术的调查结果。

作为数据处理领域的专用语言，SQL 排在了第三位，超过 50% 的开发者都需要使用到 SQL。那么，具体什么职位需要使用 SQL，用 SQL 来做什么？

数据分析师：显然这是一群依靠分析数据为生的人，必不可少需要与数据库打交道，SQL 是他们必备技能之一。
数据科学家：与数据分析师一样，数据科学家的日常工作也离不开数据的处理，不可避免需要使用 SQL。
数据库开发工程师：这个职位基本就是写 SQL 代码，实现业务逻辑。
数据库管理员：也就是 DBA，主要职责是管理和维护数据库，除了会写 SQL，还需要负责审核开发人员编写的 SQL 代码。
后端工程师：后端开发必然需要涉及数据的处理，需要通过 SQL 与数据库进行交互。
全栈工程师：既然是全栈，自然包括后端数据的处理。
移动开发工程师：作为一名移动开发工程师，一定对 SQLite 数据库不会陌生，它是在移动设备中普遍存在的嵌入式数据库。
产品经理：产品经理需要了解产品的情况，而数据是最好的说明方式，了解 SQL 非常有利于对产品的把握。

SQL 不但应用广泛，而且简单易学。因为它在设计之初就考虑了非技术人员的使用需求，SQL 语句全都是由简单的英语单词组成，使用者只需要声明自己想要的结果，而将具体的实现过程交给数据库管理系统。

学习编程，你可能会犹豫选择 C++ 还是 Java；入门数据科学，你可能会纠结于选择 Python 还是 R；但无论如何，SQL 都是 IT 从业人员不可或缺的一项技能！

本专栏主要讨论 SQL 编程技术和思想，分为四个部分：基础篇、进阶篇、开发篇以及扩展篇。

第一部分：基础篇。首先介绍数据库领域的最新发展，回顾数据库和 SQL 的核心概念；然后讨论如何使用 SELECT 语句查询数据，过滤数据、对结果进行排序、实现排行榜与分页效果；同时还会介绍常见的 SQL 函数、CASE 表达式以及数据的分组汇总；最后是一个分析世界银行全球 GDP 数据的实战案例。

第二部分：进阶篇。主要包括 SQL 数据分析的一些高级功能：空值的问题、多表连接查询、子查询、集合运算、通用表表达式与递归查询、高级分组与多维度交叉分析、窗口函数与高级报表以及基于行模式识别的数据流分析等。

第三部分：开发篇。讲述数据库设计与开发过程中涉及到的一些实用知识。包括如何设计规范化的数据库、如何管理数据库对象、如何对数据进行增删改、数据库事务的概念、索引的原理；同时还会介绍视图的概念、如何使用存储过程实现业务逻辑以及如何利用触发器实现用户操作的审计。

第四部分：扩展篇。我们将分析 SQL 语句的执行计划与查询语句的优化、使用 SQL 处理 JSON 数据、在 Python 和 Java 中执行 SQL 语句，并介绍动态语句和 SQL 注入攻击的预防。在专栏的最后，我们将探讨一下 SQL 编程中的道与术。

SQL的世界里一切都是关系

本篇我们将会介绍 SQL 的基本特性以及最重要的一个编程思想：一切都是关系。让我们先来回顾一下关系数据库的几个基本概念。

关系数据库
关系数据库（Relational database）是指基于关系模型的数据库。关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。
数据结构
在关系模型中，用于存储数据的逻辑结构称为关系（Relation）；对于使用者而言，关系就是二维表（Table）。
以下是一个员工信息表，它和 Excel 表格非常类似，由行（Row）和列（Column）组成。

在不同的场景下，大家可能会听到关于同一个概念的不同说法。在此，我们列出了关系数据库中的一些常见概念：

关系，也称为表，用于表示现实世界中的实体（Entity）或者实体之间的联系（Relationship）。举例来说，一个公司的员工、部门和职位都是实体，分别对应员工信息表、部门信息表和职位信息表；销售的产品和订单都是实体，同时它们之间存在联系，对应订单明细表。
行，也称为记录（Record），代表了关系中的单个实体。上图中工号为 4 的数据行存储了“诸葛亮”的相关信息。关系（表）可以看作是由行组成的集合。
列，也称为字段（Field），表示实体的某个属性。上图中的第二列包含了员工的姓名。表中的每个列都有一个对应的数据类型，常见的数据类型包括字符类型、数字类型、日期时间类型等。

有了关系结构之后，就需要定义基于关系的数据操作。

操作集合

常见的数据操作包括增加（Create）、查询（Retrieve）、更新（Update）以及删除（Delete），或者统称为增删改查（CRUD）。

其中，使用最多、也最复杂的操作就是查询，具体来说包括选择（Selection）、投影（Projection）、并集（Union）、交集（Intersection）、差集（exception）以及笛卡儿积（Cartesian product）等。我们将会介绍如何使用 SQL 语句完成以上各种数据操作。

为了维护数据的完整性或者满足业务需求，关系模型还定义了完整性约束。

关系性约束

关系模型中定义了三种完整性约束：实体完整性、参照完整性以及用户定义完整性。

实体完整性是指表的主键字段不能为空。现实中的每个实体都具有唯一性，比如每个人都有唯一的身份证号；在关系数据库中，这种唯一标识每一行数据的字段称为主键（Primary Key），主键字段不能为空。每个表可以有且只能有一个主键。
参照完整性是指外键参照的完整性。外键（Foreign Key）代表了两个表之间的关联关系，比如员工属于某个部门；因此员工表中存在部门编号字段，引用了部门表中的部门编号字段。对于外键引用，被引用的数据必须存在，员工不可能属于一个不存在的部门；删除某个部门之前，也需要对部门中的员工进行相应的处理。
用户定义完整性是指基于业务需要自定义的约束。非空约束（NOT NULL）确保了相应的字段不会出现空值，例如员工一定要有姓名；唯一约束（UNIQUE）用于确保字段中的值不会重复，每个员工的电子邮箱必须唯一；检查约束（CHECK）可以定义更多的业务规则。例如，薪水必须大于 0 ，字符必须大写等；默认值（DEFAULT）用于向字段中插入默认的数据。

本专栏涉及的 4 种数据库对于这些完整性约束的支持情况如下：

数据库	非空约束	唯一约束	主键约束	外键约束	检查约束	默认值
Oracle	支持	支持	支持	支持	支持	支持
MySQL	支持	支持	支持	支持*	支持*	支持
SQL Server	支持	支持	支持	支持	支持	支持
PostgreSQL	支持	支持	支持	支持	支持	支持

MySQL 中只有 InnoDB 存储引擎支持外键约束；MySQL 8.0.16 增加了对检查约束的支持。

存储引擎（Storage Engine）是 MySQL 中用于管理、访问和修改物理数据的组件，不同的存储引擎提供了不同的功能和特性。从 MySQL 5.5 开始默认使用 InnoDB 存储引擎，支持事务处理（ACID）、行级锁定、故障恢复、多版本并发控制（MVCC）以及外键约束等。

关系数据库使用 SQL 作为访问和操作数据的标准语言。现在，让我们来直观感受一下 SQL 语句的特点。

SQL：一种面向集合的编程语言

本节会出现几个示例，我们还没有正式开始学习 SQL 语句，可以暂时不必理会细节。

语法特性

SQL 是一种声明性的编程语言，语法接近于自然语言（英语）。通过几个简单的英文单词，例如 SELECT、INSERT、UPDATE、CREATE、DROP 等，完成大部分的数据库操作。以下是一个简单的查询示例：

SELECT emp_id, emp_name, salary
  FROM employee
 WHERE salary > 10000
 ORDER BY emp_id;

即使没有学过 SQL 语句，但只要知道几个单词的意思，就能明白该语句的作用。它查询员工表（employee）中月薪（salary）大于 10000 的员工，返回工号、姓名以及月薪，并且按照工号进行排序。可以看出，SQL 语句非常简单直观。

以上查询中的 SELECT、FROM 等称为关键字（也称为子句），一般大写；表名、列名等内容一般小写；分号（;）表示语句的结束。SQL 语句不区分大小写，但是遵循一定的规则可以让代码更容易阅读。

SQL 是一种声明式的语言，声明式语言的主要思想是告诉计算机想要什么结果（what），但不指定具体怎么做。这类语言还包括 HTML、正则表达式以及函数式编程等。

面向集合

对于 SQL 语句而言，它所操作的对象是一个集合（表），操作的结果也是一个集合（表）。例如以下查询：

1 2	SELECT emp_id, emp_name, salary FROM employee;

其中 employee 是一个表，它是该语句查询的对象；同时，查询的结果也是一个表。所以，我们可以继续扩展该查询：

SELECT emp_id, emp_name, salary
  FROM (
       SELECT emp_id, emp_name, salary
         FROM employee
       ) dt;

我们将括号中的查询结果（取名为 dt）作为输入值，传递给了外面的查询；最终整个语句的结果仍然是一个表。在第 17 篇中，我们将会介绍这种嵌套在其他语句中的查询就是子查询（Subquery）。

SQL 中的查询可以完成各种数据操作，例如过滤转换、分组汇总、排序显示等；但是它们本质上都是针对表的操作，结果也是表。

不仅仅是查询语句，SQL 中的插入、更新和删除都以集合为操作对象。我们再看一个插入数据的示例：

CREATE TABLE t(id INTEGER);

-- 适用于 MySQL、SQL Server 以及 PostgreSQL
INSERT INTO t(id)
VALUES (1), (2), (3);

我们首先使用 CREATE TABLE 语句创建了一个表，然后使用 INSERT INTO 语句插入数据。在执行插入操作之前，会在内存中创建一个包含 3 条数据的临时集合（表），然后将该集合插入目标表中。由于我们通常一次插入一条数据，以为是按照数据行进行插入；实际上，一条数据也是一个集合，只不过它只有一个元素而已。

Oracle 不支持以上插入多行数据的语法，可以使用下面的插入语句：

-- 适用于 Oracle
INSERT INTO t(id)
SELECT 1 FROM DUAL
 UNION ALL
SELECT 2 FROM DUAL
 UNION ALL
SELECT 3 FROM DUAL;

UNION ALL 是 SQL 中的并集运算，用于将两个集合组成一个更大的集合。此外，SQL 还支持交集运算（INTERSECT）、差集运算（EXCEPT）以及笛卡儿积（Cartesian product）。我们会在第 18 篇中介绍这些内容，它们也都是以集合为对象的操作。

我们已经介绍了 SQL 语言的声明性和面向集合的编程思想。在正式学习编写 SQL 语句之前，还需要进行一些准备工作，主要就是安装示例数据库。

示例数据库

在本专栏的学习过程中，我们主要使用一个虚构的公司数据模型。该示例数据库包含 3 个表：员工表（employee）、部门表（department）和职位表（job）。以下是它们的结构图，也称为实体-关系图（Entity-Relational Diagram）：

部门表（department），包含部门编号（deptid）和部门名称（deptname）字段，主键为部门编号。该表共计 6 条数据。
职位表（job），包含职位编号（jobid）和职位名称（jobtitle）字段，主键为职位编号。该表共计 10 条数据。
员工表（employee），包含员工编号（empid）和员工姓名（empname）等字段，主键为员工编号，部门编号（deptid）字段是引用部门表的外键，职位编号（jobid）字段是引用职位表的外键，经理编号（manager）字段是引用员工表自身的外键。该表共计 25 条数据。

我们在 GitHub 上为大家提供了示例表和初始数据的创建脚本和安装说明，支持 Oracle、MySQL、SQL Server 以及 PostgreSQL。点击链接进行下载。

运行这些脚本之前，需要先安装数据库软件。网络上有很多这类安装教程可以参考；如果无法安装数据库，也可以使用这个免费的在线 SQL 开发环境：http://sqlfiddle.com，它提供了各种常见的关系数据库服务。下图是使用 MySQL 运行示例脚本的结果：

选择数据库之后，将创建表和插入数据的脚本复制到左边窗口，点击“Build Schema”进行初始化；点击“Browser”可以查看表结构；在右侧窗口输入 SQL 语句，点击“Run SQL”运行并查看结果。该工具提供的数据库不是最新版本，但是可以运行大部分的示例。

本专栏中所有的示例都在以下数据库版本中进行了验证：

Oracle database 18c
MySQL 8.0
SQL Server 2017
PostgreSQL 12

我们使用 DBeaver 开发工具编写所有的 SQL 语句，该工具的安装和使用可以参考我的博客文章。当然，你也可以使用自己喜欢的开发工具。

小结

关系模型中定义了一个简单的数据结构，即关系（表），用于存储数据。SQL 是关系数据库的通用标准语言，它使用接近于自然语言（英语）的语法，通过声明的方式执行数据定义、数据操作、访问控制等。对于 SQL 而言，一切都是关系（表）。

参考文献

[美] Abraham Silberschatz，Henry F.Korth，S.Sudarshan 著，杨冬青，李红燕，唐世渭译，《数据库系统概念（原书第6版）》，机械工业出版社，2012

SELECT 初步探索

在 employee 表中，存储了关于员工的信息。假设现在打算群发邮件，需要找出所有员工的姓名、性别和电子邮箱。在 SQL 中可以通过一个简单的查询语句来实现：

1 2	SELECT emp_name, sex, email FROM employee;

其中 SELECT 表示查询，随后列出需要返回的字段，多个字段使用逗号分隔；FROM 表示要从哪个表中进行查询；分号表示 SQL 语句的结束。该语句执行的结果如下（显示部分数据）：

这种查询表中指定字段的操作在关系运算中被称为投影（Projection），使用 SELECT 子句进行表示。投影是针对表进行的垂直选择，保留需要的字段用于生成新的表。以下是投影操作的示意图：

投影操作中包含一个特殊的操作，就是查询表中所有的字段。

查询全部字段

查看表中的全部字段可以使用一个简单的写法，就是使用星号（*）表示全部字段。例如，以下语句查询员工表中的所有数据：

1 2	SELECT * FROM employee;

数据库在解析该语句时，会使用表中的字段名进行扩展：

1
2
3

SELECT emp_id, emp_name, sex, dept_id, manager,
       hire_date, job_id, salary, bonus, email
  FROM employee;

该语句执行的结果如下（显示部分数据）：

注意：星号可以便于快速编写查询语句，但是在实际项目中不要使用这种写法。因为应用程序可能并不需要所有的字段，避免返回过多的无用数据；另外，当表结构发生变化时，星号返回的信息也会发生改变。

除了查询表的字段之外，SELECT 语句还支持扩展的投影操作，包括基于字段的算术运算、函数和表达式等。

扩展操作

以下示例返回了员工的姓名、一年的工资（12 个月的月薪）以及电子邮箱的大写形式：

SELECT emp_name,
       salary * 12,
       UPPER(email)
  FROM employee;

其中 UPPER 是 SQL 中将字符串转换为大写的函数，函数将在第 8 篇中进行介绍。该语句的结果如下（显示部分数据）：

在上面的结果中，返回字段的名称不是很好理解；能不能给它指定一个更明确的标题呢？这就需要使用到 SQL 中的别名（Alias）功能了。

使用别名

为了提高查询结果的可读性，可以使用别名为表或者字段指定一个临时的名称。SQL 中使用关键字 AS 指定别名。我们为上面的示例指定一些更好理解的标题：

SELECT e.emp_name AS "姓名",
       salary * 12 AS "工资",
       UPPER(email) "电子邮箱"
  FROM employee AS e; -- Oracle 需要去掉此处的 AS

别名中的关键字 AS 可以省略。对于 Oracle 而言，表别名不支持 AS 关键字，省略掉即可。

首先，我们为 employee 表指定了一个表别名 e；然后为查询的结果字段指定了 3 个更明确的列别名（使用双引号）。在查询中为表指定别名之后，引用表中的字段时可以加上别名限定，例如 e.emp_name，表示要查看哪个表中的字段。以下是使用别名之后的效果：

在 SQL 语句中使用别名不会修改数据库中存储的表名或者列名，别名只在当前语句中生效。

在上面的示例中，我们还用到了 SQL 中的另一个功能：注释。

SQL 注释

在 SQL 中可以像其他编程语言一样使用注释；注释可以方便我们理解代码的作用，但不会被执行。

SQL中的注释分为单行注释和多行注释。单行注释以两个连字符（–）开始，直到这一行结束；上一节中的示例就使用了单行注释。SQL 使用 C 语言风格的多行注释（/* … */），例如：

SELECT e.emp_name AS "姓名",
       salary * 12 AS "工资",
       UPPER(email) "电子邮箱"
/* 备注：SQL 别名使用示例
   作者：TonyDong
   日期：2019-11-01
*/
  FROM employee AS e;

MySQL中的 # 也可以用于表示单行注释。

在 SQL 中，SELECT … FROM … 是最基本的查询形式；但是，有时候我们会看到一种更简单的查询：只有 SELECT 子句，没有 FROM 子句的查询。

无表查询

以下查询没有 FROM 子句，用于计算一个表达式的值：

-- MySQL、SQL Server 以及 PostgreSQL 实现
SELECT 1+1;

1+1|
---|
  2|

这种形式的查询语句通常用于快速查找信息，或者当作计算器使用。但是需要注意的是这种语法并不属于 SQL 标准，而是数据库产品自己的扩展。MySQL、SQL Server 以及 PostgreSQL 都支持无表查询；对于 Oracle 而言，可以使用以下等价的形式：

-- Oracle 实现
SELECT 1+1
  FROM dual;

1+1|
---|
  2|

dual 是 Oracle 中的一个特殊的表；它只有一个字段且只包含一行数据，就是为了方便快速查询信息。另外，MySQL 也提供了 dual 表。

小结

本篇我们学习了如何使用 SELECT 和 FROM 查询表中的数据，通过投影操作获取指定的字段信息。SQL 不仅仅能够查询表中的数据，还可以返回算术运算、函数和表达式的结果。在许多数据库中，不包含 FROM 子句的无表查询可以用于快速获取信息。另外，别名和注释都可以让我们编写的 SQL 语句更易阅读和理解。

练习题：查询部门表（department）和职位表（job）中的数据，熟悉它们的字段结构和内容。

参考文献

SQL 编程风格指南。

Mars

SQL 从入门到精通

Structured Query Language

背景

NoSQL

NewSQL

为什么要学习 SQL？

SQL的世界里一切都是关系

SELECT 初步探索

查询全部字段

使用别名

SQL 注释

无表查询

小结

参考文献